package com.guet.niit.Admin.dao;

import com.guet.niit.Admin.pojo.Agent;
import com.guet.niit.Admin.pojo.Charging;
import com.guet.niit.util.JDBCUtil;
import javafx.scene.control.Alert;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class ChargingDaolmpl{


    /**
     * 充电站添加
     * 1.SQL: "INSERT INTO charging_stations (id,city,station_name,address,agent_id,station_details,parking_fee,have_parking_fee,rent,status) "+
     *                         "VALUES("
     *                         + charging.getId()
     *                         + ",'"
     *                         + charging.getCity()
     *                         + "','"
     *                         + charging.getStation_name()
     *                         + "','"
     *                         + charging.getAddress()
     *                         + "','"
     *                         + charging.getAgent_id()
     *                         + "','"
     *                         + charging.getStation_details()
     *                         + "','"
     *                         + charging.getParking_fee()
     *                         + "','"
     *                         + charging.getHave_parking_fee()
     *                         + "','"
     *                         + charging.getRent()
     *                         + "','"
     *                         + charging.getStatus()
     *                         + "')";
     *
     * @return
     */
    public boolean chargingAdd(Charging charging){
            Connection conn = null;
            Statement stmt = null;
            ResultSet rs = null;
            try {
                // 获得数据的连接
                conn = JDBCUtil.createConnection();
                // 获得Statement对象
                stmt = conn.createStatement();
                // 发送SQL语句
                String sql = "INSERT INTO charging_stations (id,city,station_name,address,agent_id,station_details,parking_fee,have_parking_fee,rent,status) "+
                        "VALUES("
                        + charging.getId()
                        + ",'"
                        + charging.getCity()
                        + "','"
                        + charging.getStation_name()
                        + "','"
                        + charging.getAddress()
                        + "','"
                        + charging.getAgent_id()
                        + "','"
                        + charging.getStation_details()
                        + "','"
                        + charging.getParking_fee()
                        + "','"
                        + charging.getHave_parking_fee()
                        + "','"
                        + charging.getRent()
                        + "','"
                        + charging.getStatus()
                        + "')";
                int num = stmt.executeUpdate(sql);
                if (num > 0) {
                    return true;
                }
                return false;
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                JDBCUtil.closeConnection(conn,stmt,rs);
            }
            return false;
        }





    /**
     * 充电站删除
     * 1.SQL:delete from 表名 where 条件;
     * 2.输入充电站id进行充电站删除
     */
    public boolean chargingDelete(Long id){

            Connection connection = null;
            Statement statement = null;
            ResultSet rs = null;
            try {
                // 获得数据的连接
                connection = JDBCUtil.createConnection();
                // 获得Statement对象
                statement = connection.createStatement();
                // 发送SQL语句
                String sql = "DELETE FROM charging_stations WHERE id=" + id;
                int num = statement.executeUpdate(sql);
                if (num > 0) {
                    return true;
                }
                return false;
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                JDBCUtil.closeConnection(connection, statement, rs);
            }
            return false;
        }



//    /**
//     * 充电站修改
//     * 1.SQL: "UPDATE charging set city='" + charging.getCity()
//     *                     + "',station_name='" + charging.getStation_name() + "',address='"
//     *                     + charging.getAddress() + "',agent_id='" + charging.getAgent_id()+ "',parking_fee='"+charging.getParking_fee()+
//     *                     "',have_parking_fee='"+charging.getHave_parking_fee()+"',rent='"+charging.getRent()+"',status='"+charging.getStatus()
//     *                     + "' WHERE id=" + charging.getId();
//     *
//     * @return
//     */
//    public boolean chargingModify(Long id, String city, String station_name, String address,
//                                  Long agent_id,String station_details,Double parking_fee,
//                                  Integer have_parking_fee,Double rent,Double status){
//        Connection conn = null;
//        Statement stmt = null;
//        ResultSet rs = null;
//        try {
//            // 获得数据的连接
//            conn = JDBCUtil.createConnection();
//            // 获得Statement对象
//            stmt = conn.createStatement();
//            // 发送SQL语句
//            Charging charging = new Charging(city,id,station_name,agent_id,address,
//                    station_details,parking_fee,have_parking_fee,status,rent);
//            String sql =  "UPDATE charging_stations set city='" + charging.getCity()
//                    + "',station_name='" + charging.getStation_name() + "',address='"
//                    + charging.getAddress() + "',agent_id='" + charging.getAgent_id()+ "',parking_fee='"+charging.getParking_fee()+
//                    "',have_parking_fee='"+charging.getHave_parking_fee()+"',rent='"+charging.getRent()+"',status='"+charging.getStatus()
//                    + "' WHERE id=" + charging.getId();
//
//            int num = stmt.executeUpdate(sql);
//            if (num > 0) {
//                return true;
//            }
//            return false;
//        } catch (Exception e) {
//            e.printStackTrace();
//        } finally {
//            JDBCUtil.closeConnection(conn, stmt,rs );
//        }
//        return false;
//    }


    /**
     * 充电站修改
     * @param charging
     * @return
     */
    public String chargingModify(Charging charging){
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            // 获得数据的连接
            conn = JDBCUtil.createConnection();
            // 设置SQL语句
            String sql = "update charging_stations set city = ? , station_name = ? , agent_id = ? , address = ? ," +
                    "station_details = ? , parking_fee = ? , have_parking_fee = ? , status = ? , rent = ? where id = ?" ;
            // 获得PreparedStatement对象
            PreparedStatement pstmt = conn.prepareStatement(sql);
            //设置参数
            pstmt.setString(1,charging.getCity());
            pstmt.setString(2,charging.getStation_name());
            pstmt.setLong(3,charging.getAgent_id());
            pstmt.setString(4,charging.getAddress());
            pstmt.setString(5,charging.getStation_details());
            pstmt.setDouble(6,charging.getParking_fee());
            pstmt.setInt(7,charging.getHave_parking_fee());
            pstmt.setDouble(8,charging.getStatus());
            pstmt.setDouble(9,charging.getRent());
            pstmt.setLong(10,charging.getId());
            //执行sql
            pstmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeConnection(conn, stmt,rs );
        }
        return "success";
    }

    /**
     * 充电站查询（查询charging_stations表中的）
     * 1.SQl:select * from charging_stations;
     * 2.结果：List<arrayList>
     */
    public List<Charging> chargingInquire(){
        Connection connection = null;
        try {
            connection = JDBCUtil.createConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }
        Statement statement = null;
        ResultSet rs = null;
        Charging charging = null;
       List<Charging> arrayList;
        try {

            PreparedStatement preparedStatement = null;
            String sql = "select * from charging_stations";
            preparedStatement = connection.prepareStatement(sql);
            rs = preparedStatement.executeQuery();
            arrayList = new ArrayList<>();
            while (rs.next()) {
                String station_name = rs.getString("station_name");
                Long id = rs.getLong("id");
                String city = rs.getString("city");
                String address = rs.getString("address");
                Long agent_id = rs.getLong("agent_id");
                String station_details = rs.getString("station_details");
                Double parking_fee = rs.getDouble("parking_fee");
                Integer have_parking_fee = rs.getInt("have_parking_fee");
                Double status = rs.getDouble("status");
                Double rent = rs.getDouble("rent");
                charging = new Charging(city,id,station_name,agent_id,address,
                        station_details,parking_fee,have_parking_fee,status,rent);
                arrayList.add(charging);
            }
        } catch (SQLException e) {
            throw new RuntimeException();
        } finally {
            JDBCUtil.closeConnection(connection, statement, rs);
        }

        return arrayList;


    }

    /**
     * 查询指定充电站名通过在充电桩表获取充电站id查询
     * @return
     */
    public List<Charging> selectStationBeforeSelectStationPile() {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        Charging charging= null;
        ArrayList<Charging> chargings = new ArrayList<>();

        try {
            Connection conn = JDBCUtil.createConnection();
            String sql = "select charging_stations.station_name from charging_stations inner join charging_pile on charging_stations.id = charging_pile.station_id";
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()){
                //获取数据
                String StationName = rs.getString("Station_name");
//                long id = rs.getLong("id");
//                String username = rs.getString("username");
//                String password = rs.getString("password");
//                String photo = rs.getString("photo");
                //封装login对象
//                agent = new Agent(id,username,password,photo);
                charging = new Charging();
                charging.setStation_name(StationName);
                //装载集合
                chargings.add(charging);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.closeConnection(connection,statement,resultSet);
        }
        return chargings;
    }

    /**
     * 城市查询
     * @return
     */
//    public List<Charging> chargingInquireByCity(){
    public List<Charging> chargingInquireByCity(String queryCity){
        Connection connection = null;
        try {
            connection = JDBCUtil.createConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }
        Statement statement = null;
        ResultSet rs = null;
        Charging charging = null;
        //String city = new String();
        //Charging charging1 = null;
        List<Charging> arrayList;
        try {
            PreparedStatement preparedStatement = null;
            //String city = new String();//
//            System.out.println("请输入你需要查询的城市:");
//            Scanner scanner = new Scanner(System.in);
//            String city = scanner.next();
            String sql = "select * from charging_stations where city =\""+queryCity+"\"";
            //System.out.println(sql);
            preparedStatement = connection.prepareStatement(sql);
            rs = preparedStatement.executeQuery();
            arrayList = new ArrayList<>();
            while (rs.next()) {
                String city = rs.getString("city");
                String station_name = rs.getString("station_name");
                Long id = rs.getLong("id");
                String address = rs.getString("address");
                Long agent_id = rs.getLong("agent_id");
                String station_details = rs.getString("station_details");
                Double parking_fee = rs.getDouble("parking_fee");
                int have_parking_fee = rs.getInt("have_parking_fee");
                Double status = rs.getDouble("status");
                Double rent = rs.getDouble("rent");
                charging = new Charging(city, id,station_name,agent_id,address,
                        station_details,parking_fee,have_parking_fee,status,rent);
                arrayList.add(charging);
            }
        } catch (SQLException e) {
            throw new RuntimeException();
        } finally {
            JDBCUtil.closeConnection(connection, statement, rs);
        }

        return arrayList;

    }

    /**
     *有无停车费查询
     */

//    public List<Charging> chargingInquireByParkingFee(){
    public List<Charging> chargingInquireByParkingFee(int haveParkingFee){
        Connection connection = null;
        try {
            connection = JDBCUtil.createConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }
        Statement statement = null;
        ResultSet rs = null;
        Charging charging = null;
        //String city = new String();
        //Charging charging1 = null;
        List<Charging> arrayList;
        try {
            PreparedStatement preparedStatement = null;
            //String city = new String();//
//            System.out.println("需要查询有停车费请输入1/需要查询无停车费请输入0:");
//            Scanner scanner = new Scanner(System.in);
//            int have_parking_fee = scanner.nextInt();
            String sql = "select * from charging_stations where have_parking_fee =\" "+haveParkingFee+"\"";
            //System.out.println(sql);
            preparedStatement = connection.prepareStatement(sql);
            rs = preparedStatement.executeQuery();
            arrayList = new ArrayList<>();
            while (rs.next()) {
                String station_name = rs.getString("station_name");
                Long id = rs.getLong("id");
                String city = rs.getString("city");
                String address = rs.getString("address");
                Long agent_id = rs.getLong("agent_id");
                String station_details = rs.getString("station_details");
                Double parking_fee = rs.getDouble("parking_fee");
                Integer have_parking_fee = rs.getInt("have_parking_fee");
                Double status = rs.getDouble("status");
                Double rent = rs.getDouble("rent");
                charging = new Charging(city, id,station_name,agent_id,address,
                        station_details,parking_fee,have_parking_fee,status,rent);
                arrayList.add(charging);
            }
        } catch (SQLException e) {
            throw new RuntimeException();
        } finally {
            JDBCUtil.closeConnection(connection, statement, rs);
        }

        return arrayList;
    }

    /**
     * 通过充电站名查询充电站详情
     * @param stationName
     * @return
     */
    public Charging selectStationDetailsByStationName(String stationName){
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        Charging charging = null;
//        List<Agent> agents = new ArrayList<>();

        try {
            Connection conn = JDBCUtil.createConnection();
            String sql = "select station_details from charging_stations where station_name = \""+stationName+"\"";
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()){
                //获取数据
                String stationDetails = rs.getString("station_details");
//                long id = rs.getLong("id");
//                String username = rs.getString("username");
//                String password = rs.getString("password");
//                String photo = rs.getString("photo");
                //封装login对象
//                agent = new Agent(id,username,password,photo);
                charging = new Charging();
                charging.setStation_details(stationDetails);
                //装载集合
//                agents.add(agent);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.closeConnection(connection,statement,resultSet);
        }
        return charging;
    }

    /**
     * 通过充电站名查询充电站id
     * @param stationName
     * @return
     */
    public Charging selectStationIdByStationName(String stationName){
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        Charging charging = null;
//        List<Agent> agents = new ArrayList<>();

        try {
            Connection conn = JDBCUtil.createConnection();
            String sql = "select id from charging_stations where station_name = \""+stationName+"\"";
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()){
                //获取数据
                Long stationId = rs.getLong("id");
//                long id = rs.getLong("id");
//                String username = rs.getString("username");
//                String password = rs.getString("password");
//                String photo = rs.getString("photo");
                //封装login对象
//                agent = new Agent(id,username,password,photo);
                charging = new Charging();
                charging.setId(stationId);
                //装载集合
//                agents.add(agent);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.closeConnection(connection,statement,resultSet);
        }
        return charging;
    }

}
